﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace DATA
{
    public class MetodoDatos
    {
        public static Conexion strCNN = new Conexion();
        public static    SqlConnection cn = new SqlConnection(strCNN.obtenerConexion() );
        //n.Open();
        //static SqlConnection cn = Conexion.conectar();
        static SqlTransaction trans;

        public static SqlCommand CrearComando(string procedure)
        {
            cn.Open();
            trans = cn.BeginTransaction();

            SqlCommand cmd = new SqlCommand(procedure, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Transaction = trans;
            return cmd;
        }

        public static void ejecutarComando(SqlCommand cmd)
        {
            int records = 0;
            try
            {
                records = cmd.ExecuteNonQuery();
                if (records != 0)
                {
                    trans.Commit();
                }
                else
                {
                    trans.Rollback();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cn.Close();
            }
        }

        public static void abrirConexion()
        {
            cn.Open();
        }

        public static void cerrarConexion()
        {
            cn.Close();
        }

        public static DataTable listaDT(string procedure)
        {
            DataTable dt = null;
            SqlDataAdapter da = null;
            try
            {
                dt = new DataTable();
                cn.Open();
                da = new SqlDataAdapter(procedure, cn);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        public static DataSet listaDS(string procedure)
        {
            DataSet ds = null;
            SqlDataAdapter da = null;
            try
            {
                ds = new DataSet();
                cn.Open();
                da = new SqlDataAdapter(procedure, cn);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cn.Close();
            }
            return ds;
        }       
    }
}
